#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database if not exists oe_dm;
use oe_dm;

drop table if exists dm_attendance;
create table if not exists dm_attendance
(
    date_code                  date comment '日期',
    class_id                   int comment '班级id',
    content                    string comment '上课内容',
    studying_student_count     int comment '在读人数',
    morning_attendance_count   bigint comment '上午出勤人数',
    morning_attendance_ratio   decimal(22, 2) comment '上午出勤率',
    morning_late_count         bigint comment '上午迟到人数',
    morning_late_ratio         decimal(22, 2) comment '上午迟到率',
    afternoon_attendance_count bigint comment '下午出勤人数',
    afternoon_attendance_ratio decimal(22, 2) comment '下午出勤率',
    afternoon_late_count       bigint comment '下午迟到人数',
    afternoon_late_ratio       decimal(22, 2) comment '下午迟到率',
    evening_attendance_count   bigint comment '晚上出勤人数',
    evening_attendance_ratio   decimal(22, 2) comment '晚上出勤率',
    evening_late_count         bigint comment '晚上迟到人数',
    evening_late_ratio         decimal(22, 2) comment '晚上迟到率'
) comment '出勤表'
    row format delimited
        fields terminated by '\t'
    stored as orc
    tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dm_leave;
create table if not exists dm_leave
(
    date_code              date comment '日期',
    class_id               int comment '班级id',
    content                string comment '上课内容',
    studying_student_count int comment '在读人数',
    morning_leave_count    bigint comment '上午请假人数',
    morning_leave_ratio    decimal(22, 2) comment '上午请假率',
    afternoon_leave_count  bigint comment '下午请假人数',
    afternoon_leave_ratio  decimal(22, 2) comment '下午请假率',
    evening_leave_count    bigint comment '晚上请假人数',
    evening_leave_ratio    decimal(22, 2) comment '晚上请假率'
) COMMENT '请假表'
    row format delimited
        fields terminated by '\t'
    stored as orc
    tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dm_absent;
create table if not exists dm_absent
(
    date_code              date comment '日期',
    class_id               int comment '班级id',
    content                string comment '上课内容',
    studying_student_count int comment '在读人数',
    morning_absent_count   bigint comment '上午旷课人数',
    morning_absent_ratio   decimal(22, 2) comment '上午旷课率',
    afternoon_absent_count bigint comment '下午旷课人数',
    afternoon_absent_ratio decimal(22, 2) comment '下午旷课率',
    evening_absent_count   bigint comment '晚上旷课人数',
    evening_absent_ratio   decimal(22, 2) comment '晚上旷课率'
) COMMENT '旷课表'
    row format delimited
        fields terminated by '\t'
    stored as orc
    tblproperties ('orc.compress' = 'SNAPPY');"